"""Database manager for storing review results and logs."""

import json
import logging
from datetime import datetime, timedelta
from pathlib import Path
from typing import Dict, List, Optional

from sqlalchemy import (
    Boolean,
    Column,
    DateTime,
    Float,
    Integer,
    String,
    Text,
    create_engine,
    func,
)
try:
    from sqlalchemy.orm import declarative_base
except ImportError:
    # For older SQLAlchemy versions
    from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

from ..models import ReviewResult

logger = logging.getLogger(__name__)

Base = declarative_base()


class ReviewedPull(Base):
    """Reviewed pull request table."""

    __tablename__ = "reviewed_pulls"

    id = Column(Integer, primary_key=True, autoincrement=True)
    repo_name = Column(String(255), nullable=False, index=True)
    pr_number = Column(Integer, nullable=False, index=True)
    pr_title = Column(String(500))
    pr_url = Column(String(500))
    reviewed_at = Column(DateTime, nullable=False, index=True)
    severity = Column(String(20))
    severity_score = Column(Float)
    review_result = Column(Text)  # JSON format
    email_sent = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.now)


class ReviewLog(Base):
    """Review execution log table."""

    __tablename__ = "review_logs"

    id = Column(Integer, primary_key=True, autoincrement=True)
    repo_name = Column(String(255))
    pr_number = Column(Integer)
    status = Column(String(20))  # success/failed
    error_message = Column(Text)
    execution_time = Column(Float)  # seconds
    created_at = Column(DateTime, default=datetime.now)


class DatabaseManager:
    """Database manager for review data."""

    def __init__(self, db_config: Dict):
        """
        Initialize database manager.

        Args:
            db_config: Database configuration dictionary
        """
        self.db_type = db_config.get("type", "sqlite")
        self.db_path = db_config.get("path", "./data/review.db")

        if self.db_type == "sqlite":
            # Ensure directory exists
            Path(self.db_path).parent.mkdir(parents=True, exist_ok=True)
            db_url = f"sqlite:///{self.db_path}"
        elif self.db_type == "postgresql":
            pg_config = db_config.get("postgresql", {})
            db_url = (
                f"postgresql://{pg_config.get('user')}:{pg_config.get('password')}"
                f"@{pg_config.get('host')}:{pg_config.get('port', 5432)}"
                f"/{pg_config.get('database')}"
            )
        else:
            raise ValueError(f"Unsupported database type: {self.db_type}")

        self.engine = create_engine(db_url, echo=False)
        self.SessionLocal = sessionmaker(bind=self.engine)
        self.init_database()

    def init_database(self) -> None:
        """Initialize database tables."""
        Base.metadata.create_all(self.engine)
        logger.info("Database initialized")

    def is_pr_reviewed_today(self, repo_name: str, pr_number: int) -> bool:
        """
        Check if PR was reviewed today.

        Args:
            repo_name: Repository name
            pr_number: Pull request number

        Returns:
            True if PR was reviewed today
        """
        return self.is_pr_reviewed_on_date(repo_name, pr_number, None)

    def is_pr_reviewed_on_date(
        self, repo_name: str, pr_number: int, target_date: Optional[datetime.date] = None
    ) -> bool:
        """
        Check if PR was reviewed on a specific date.

        Args:
            repo_name: Repository name
            pr_number: Pull request number
            target_date: Target date to check. None means today

        Returns:
            True if PR was reviewed on the target date
        """
        session = self.SessionLocal()
        try:
            if target_date is None:
                check_date = datetime.now().date()
            else:
                check_date = target_date
            
            count = (
                session.query(ReviewedPull)
                .filter(
                    ReviewedPull.repo_name == repo_name,
                    ReviewedPull.pr_number == pr_number,
                    func.date(ReviewedPull.reviewed_at) == check_date,
                )
                .count()
            )
            return count > 0
        finally:
            session.close()

    def save_review_result(self, result: ReviewResult) -> bool:
        """
        Save review result to database.

        Args:
            result: ReviewResult object

        Returns:
            True if save successful
        """
        session = self.SessionLocal()
        try:
            reviewed_pull = ReviewedPull(
                repo_name=result.repo_name,
                pr_number=result.pr_number,
                pr_title=result.pr_title,
                pr_url=result.pr_url,
                reviewed_at=result.generated_at,
                severity=result.severity,
                severity_score=result.severity_score,
                review_result=json.dumps(result.to_dict(), ensure_ascii=False),
                email_sent=False,
            )
            session.add(reviewed_pull)
            session.commit()
            logger.info(f"Saved review result for {result.repo_name}#{result.pr_number}")
            return True
        except Exception as e:
            session.rollback()
            logger.error(f"Failed to save review result: {e}")
            return False
        finally:
            session.close()

    def mark_email_sent(self, repo_name: str, pr_number: int, reviewed_at: datetime) -> None:
        """
        Mark email as sent for a review.

        Args:
            repo_name: Repository name
            pr_number: Pull request number
            reviewed_at: Review timestamp
        """
        session = self.SessionLocal()
        try:
            reviewed_pull = (
                session.query(ReviewedPull)
                .filter(
                    ReviewedPull.repo_name == repo_name,
                    ReviewedPull.pr_number == pr_number,
                    ReviewedPull.reviewed_at == reviewed_at,
                )
                .first()
            )
            if reviewed_pull:
                reviewed_pull.email_sent = True
                session.commit()
        except Exception as e:
            session.rollback()
            logger.error(f"Failed to mark email as sent: {e}")
        finally:
            session.close()

    def get_review_history(
        self, repo_name: Optional[str] = None, days: int = 7
    ) -> List[Dict]:
        """
        Get review history.

        Args:
            repo_name: Repository name filter (optional)
            days: Number of days to look back

        Returns:
            List of review records
        """
        session = self.SessionLocal()
        try:
            cutoff_date = datetime.now() - timedelta(days=days)
            query = session.query(ReviewedPull).filter(ReviewedPull.reviewed_at >= cutoff_date)

            if repo_name:
                query = query.filter(ReviewedPull.repo_name == repo_name)

            results = query.order_by(ReviewedPull.reviewed_at.desc()).all()

            return [
                {
                    "repo_name": r.repo_name,
                    "pr_number": r.pr_number,
                    "pr_title": r.pr_title,
                    "severity": r.severity,
                    "severity_score": r.severity_score,
                    "reviewed_at": r.reviewed_at.isoformat(),
                    "email_sent": r.email_sent,
                }
                for r in results
            ]
        finally:
            session.close()

    def log_review_execution(
        self,
        repo_name: Optional[str],
        pr_number: Optional[int],
        status: str,
        execution_time: float,
        error_message: Optional[str] = None,
    ) -> None:
        """
        Log review execution.

        Args:
            repo_name: Repository name
            pr_number: Pull request number
            status: Execution status (success/failed)
            execution_time: Execution time in seconds
            error_message: Error message if failed
        """
        session = self.SessionLocal()
        try:
            log = ReviewLog(
                repo_name=repo_name,
                pr_number=pr_number,
                status=status,
                error_message=error_message,
                execution_time=execution_time,
            )
            session.add(log)
            session.commit()
        except Exception as e:
            session.rollback()
            logger.error(f"Failed to log review execution: {e}")
        finally:
            session.close()

